﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Data.Objects;
using System.Data.SqlClient;
using System.Linq;
using System.Web;

namespace JamesThew.Dashboard
{
    public class Analytics
    {

        public List<String[]> AdvancedGetAnalytics(int Year, int MonthMin, int MonthMax, int DateMin, int DateMax, 
            String GroupBy, String PaymentPackage, String PaymentMethod, String PaymentStatus)
        {
            List<String[]> lp = new List<String[]>();

            SqlConnection con = new SqlConnection();
            con.ConnectionString = JamesThew.Includes.Core.GetConnectionString();
            con.Open();
            SqlCommand cmd = new SqlCommand("PaymentAnalytics", con);
            cmd.CommandType = CommandType.StoredProcedure;

            cmd.Parameters.Add(new SqlParameter("@Year", Year));
            cmd.Parameters.Add(new SqlParameter("@MonthMin", MonthMin));
            cmd.Parameters.Add(new SqlParameter("@MonthMax", MonthMax));
            cmd.Parameters.Add(new SqlParameter("@DateMin", DateMin));
            cmd.Parameters.Add(new SqlParameter("@DateMax", DateMax));
            cmd.Parameters.Add(new SqlParameter("@GroupBy", GroupBy));
            cmd.Parameters.Add(new SqlParameter("@PaymentPackage", PaymentPackage));
            cmd.Parameters.Add(new SqlParameter("@PaymentMethod", PaymentMethod));
            cmd.Parameters.Add(new SqlParameter("@PaymentStatus", PaymentStatus));
            
            SqlDataReader rd;

            rd = cmd.ExecuteReader();
            DataTable dt = new DataTable();

            dt.Load(rd);

            for (int i = 0; i < dt.Rows.Count; i++)
            {
                String Total = dt.Rows[i][1].ToString();
                String YearCreated = dt.Rows[i][2].ToString();
                String MonthCreated = dt.Rows[i][3].ToString();
                String DateCreated = "", Revenue = "";
                Revenue = dt.Rows[i][4].ToString();
                if (GroupBy == "date")
                {
                    DateCreated = dt.Rows[i][5].ToString();
                }
                String[] S = new String[] { YearCreated, MonthCreated, Total, Revenue, DateCreated };
                lp.Add(S);
            }

            con.Close();

            return lp;
        }

        public List<String[]> GeneralPaymentAnalytics(int Year, int MonthMin, int MonthMax, int DateMin, int DateMax,
            String GroupBy, String PaymentPackage, String PaymentMethod, String PaymentStatus)
        {
            List<String[]> lp = new List<String[]>();

            SqlConnection con = new SqlConnection();
            con.ConnectionString = JamesThew.Includes.Core.GetConnectionString();
            con.Open();
            SqlCommand cmd = new SqlCommand("GPAnalytics", con);
            cmd.CommandType = CommandType.StoredProcedure;

            cmd.Parameters.Add(new SqlParameter("@Year", Year));
            cmd.Parameters.Add(new SqlParameter("@MonthMin", MonthMin));
            cmd.Parameters.Add(new SqlParameter("@MonthMax", MonthMax));
            cmd.Parameters.Add(new SqlParameter("@DateMin", DateMin));
            cmd.Parameters.Add(new SqlParameter("@DateMax", DateMax));
            cmd.Parameters.Add(new SqlParameter("@GroupBy", GroupBy));
            cmd.Parameters.Add(new SqlParameter("@PaymentPackage", PaymentPackage));
            cmd.Parameters.Add(new SqlParameter("@PaymentMethod", PaymentMethod));
            cmd.Parameters.Add(new SqlParameter("@PaymentStatus", PaymentStatus));

            SqlDataReader rd;

            rd = cmd.ExecuteReader();
            DataTable dt = new DataTable();

            dt.Load(rd);

            for (int i = 0; i < dt.Rows.Count; i++)
            {
                String Plan = dt.Rows[i][2].ToString();
                String Revenue = dt.Rows[i][1].ToString();
                String Total = dt.Rows[i][0].ToString();

                String[] S = new String[] { Plan, Total, Revenue };
                lp.Add(S);
            }

            con.Close();

            return lp;
        }

        public List<String[]> GetPaymentInfo() {
            List<String[]> ls = new List<String[]>();

            SqlConnection con = new SqlConnection();
            con.ConnectionString = JamesThew.Includes.Core.GetConnectionString();
            con.Open();
            SqlCommand cmd = new SqlCommand();
            cmd.Connection = con;
            cmd.CommandText = "SELECT 'yearly' AS PaymentPackage, Year(DateCreated) as iYear, Month(DateCreated) iMonth, COUNT(*) AS Total, COUNT(*)*100 AS Revenue FROM CMS_User_Payment WHERE PaymentPackage = 'yearly' AND PaymentStatus = 'approved' GROUP BY Year(DateCreated), Month(DateCreated)";
            SqlDataReader rd;

            rd = cmd.ExecuteReader();
            DataTable dt = new DataTable();

            dt.Load(rd);

            for (int i = 0; i < dt.Rows.Count; i++)
            {
                String PaymentPackage = dt.Rows[i][0].ToString();
                String Year = dt.Rows[i][1].ToString();
                String Month = dt.Rows[i][2].ToString();
                String Date = dt.Rows[i][3].ToString();
                String Revenue = dt.Rows[i][4].ToString();
                String[] S = new String[] { PaymentPackage, Year, Month, Date, Revenue };
                ls.Add(S);
            }

            con.Close();

            return ls;
        }

        public List<String[]> GetMonthyPaymentInfo()
        {
            List<String[]> ls = new List<String[]>();

            SqlConnection con = new SqlConnection();
            con.ConnectionString = JamesThew.Includes.Core.GetConnectionString();
            con.Open();
            SqlCommand cmd = new SqlCommand();
            cmd.Connection = con;
            cmd.CommandText = "SELECT 'monthy' AS PaymentPackage, Year(DateCreated) as iYear, Month(DateCreated) iMonth, COUNT(*) AS Total, COUNT(*)*10 AS Revenue FROM CMS_User_Payment WHERE PaymentPackage = 'monthy' AND PaymentStatus = 'approved' GROUP BY Year(DateCreated), Month(DateCreated)";
            SqlDataReader rd;

            rd = cmd.ExecuteReader();
            DataTable dt = new DataTable();

            dt.Load(rd);

            for (int i = 0; i < dt.Rows.Count; i++)
            {
                String PaymentPackage = dt.Rows[i][0].ToString();
                String Year = dt.Rows[i][1].ToString();
                String Month = dt.Rows[i][2].ToString();
                String Date = dt.Rows[i][3].ToString();
                String Revenue = dt.Rows[i][4].ToString();
                String[] S = new String[] { PaymentPackage, Year, Month, Date, Revenue };
                ls.Add(S);
            }

            con.Close();

            return ls;
        }

        public List<String[]> GetGeneralPaymentData()
        {
            List<String[]> ls = new List<String[]>();

            SqlConnection con = new SqlConnection();
            con.ConnectionString = JamesThew.Includes.Core.GetConnectionString();
            con.Open();
            SqlCommand cmd = new SqlCommand();
            cmd.Connection = con;
            cmd.CommandText = "SELECT COUNT(*) AS Posts, PostType FROM CMS_Posts GROUP BY PostType UNION ALL SELECT COUNT(*) as Posts, 'all' as PostType FROM CMS_Posts";
            SqlDataReader rd;

            rd = cmd.ExecuteReader();
            DataTable dt = new DataTable();

            dt.Load(rd);

            for (int i = 0; i < dt.Rows.Count; i++)
            {
                String PostType = dt.Rows[i][1].ToString();
                String NumOfPost = dt.Rows[i][0].ToString();
                String[] S = new String[] { PostType, NumOfPost };
                ls.Add(S);
            }

            con.Close();

            return ls;
        }

        public List<String[]> GetPostsData()
        {
            List<String[]> ls = new List<String[]>();

            SqlConnection con = new SqlConnection();
            con.ConnectionString = JamesThew.Includes.Core.GetConnectionString();
            con.Open();
            SqlCommand cmd = new SqlCommand();
            cmd.Connection = con;
            cmd.CommandText = "SELECT COUNT(*) AS Posts, PostType FROM CMS_Posts GROUP BY PostType UNION ALL SELECT COUNT(*) as Posts, 'all' as PostType FROM CMS_Posts";
            SqlDataReader rd;

            rd = cmd.ExecuteReader();
            DataTable dt = new DataTable();

            dt.Load(rd);

            for (int i = 0; i < dt.Rows.Count; i++)
            {
                String PostType = dt.Rows[i][1].ToString();
                String NumOfPost = dt.Rows[i][0].ToString();
                String[] S = new String[] { PostType, NumOfPost };
                ls.Add(S);
            }

            con.Close();

            return ls;
        }

        public List<String[]> GetCommentsData()
        {
            List<String[]> ls = new List<String[]>();

            SqlConnection con = new SqlConnection();
            con.ConnectionString = JamesThew.Includes.Core.GetConnectionString();
            con.Open();
            SqlCommand cmd = new SqlCommand();
            cmd.Connection = con;
            cmd.CommandText = "SELECT COUNT(*) AS Comments, CommentApproved FROM CMS_Comments GROUP BY CommentApproved UNION ALL SELECT COUNT(*) AS Comments, '3' AS CommentApproved FROM CMS_Comments";
            SqlDataReader rd;

            rd = cmd.ExecuteReader();
            DataTable dt = new DataTable();

            dt.Load(rd);

            for (int i = 0; i < dt.Rows.Count; i++)
            {
                String NumOfComment = dt.Rows[i][1].ToString();
                String CommentStatus = dt.Rows[i][0].ToString();
                String[] S = new String[] { NumOfComment, CommentStatus };
                ls.Add(S);
            }

            con.Close();

            return ls;
        }

        public String[] GetNotifications() {
            String[] Notifications = new String[7];


            return Notifications;
        }

    }
}